﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using Pms.Utils;
using Biblioteca.Utils;

namespace SERIX.Classes
{
    internal struct StructProgramacao
    {
        public int idt_Ind;
        public int? Idt_Tpo_Med;
        public int Idt_AIS;
        public decimal? valor;
        public int? Idt_Tpo_Med2;
        public decimal? valor2;
        public int? Idt_Tpo_Med3;
        public decimal? valor3;

        public override bool Equals(object obj)
        {
            if (obj is StructProgramacao)
            {
                StructProgramacao prg = (StructProgramacao)obj;
                if (prg.idt_Ind == this.idt_Ind
                    && prg.Idt_Tpo_Med == this.Idt_Tpo_Med
                    && prg.Idt_AIS == this.Idt_AIS
                    //&& prg.ordem_Mir == this.ordem_Mir
                    )
                    return true;
                //return base.Equals(obj);
            }
            return false;
        }
    }


    [DataObject(true)]
    public class CProgramacao
    {
        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable ObterMunicipios(int idt_Tec, string login)
        {
            string sql = @"select mun.cod_IBGE || ' - ' || mun.nome_mun NOME_MUN,
mun.idt_mun from srx_tecnicos tec
  join srx_lotacoes_tecnicos lotTec on lottec.idt_tec = tec.idt_tec
  join srx_municipios mun on mun.idt_mun = lottec.idt_mun
where lottec.sta_lotacao = 'A' AND tec.idt_tec = :idt_tec

union

SELECT U.COD_IBGE ||' - '|| U.NOME_MUN NOME_MUN, U.IDT_MUN FROM SRX_MUNICIPIOS U
                            join SRX_USUARIOS_MUNICIPIOS S ON (U.IDT_MUN = S.IDT_MUN) 
                                AND (S.STATUS = 'A') AND (S.IDT_TEC = :IDT_TEC)";
            OracleCommand command = new OracleCommand();
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = sql;

                command.Parameters.Add(new OracleParameter("idt_tec", OracleType.Number));

                command.Parameters["idt_tec"].Value = idt_Tec;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }

        [System.ComponentModel.DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable ObterProgramas(string ano_AIS, string idt_Mun, string login)
        {
            if (string.IsNullOrEmpty(ano_AIS) || string.IsNullOrEmpty(idt_Mun))
                return new DataTable();
            string sql = @"select prog.idt_prog, prog.NOM_PROG from srx_programas_municipios pms
  join srx_programas prog on prog.idt_prog = pms.idt_prog
where pms.pms_ano = :pms_ano and pms.idt_mun = :idt_mun
order by prog.NOM_PROG";
            OracleCommand command = new OracleCommand();
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = sql;

                command.Parameters.Add(new OracleParameter("pms_ano", OracleType.Number));
                command.Parameters.Add(new OracleParameter("idt_Mun", OracleType.Number));

                command.Parameters["pms_ano"].Value = ano_AIS;
                command.Parameters["idt_Mun"].Value = idt_Mun;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }

        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable ObterProgramacao(string idt_prog, string ano_Ais, string idt_Mun,
            string idt_Acao, string idt_tec, string login)
        {
            //idt_prog = "1";
            //ano_Ais = "2010";
            //idt_Mun = "2";
            //idt_Acao = "1";
            if (string.IsNullOrEmpty(idt_prog) || string.IsNullOrEmpty(ano_Ais)
            || string.IsNullOrEmpty(idt_Mun) || string.IsNullOrEmpty(idt_Acao))
                return new DataTable();
            string sql = @"select ais.idt_AIS, ais.Idt_Prog, Ano_Ais, ais.idt_acao, ais.Idt_Ind, ind.dsc_ind,
  valor_Prog, valor_Prog2, valor_Prog3, 
  nvl(Idt_TPo_MEd, (select Idt_TPo_MEd from srx_medidas_indicadores medInd
        where medind.idt_ind = ind.idt_ind and medind.ordem_mir = 1)) Idt_TPo_MEd,
  nvl(Idt_TPo_MEd2, (select Idt_TPo_MEd from srx_medidas_indicadores medInd
        where medind.idt_ind = ind.idt_ind and medind.ordem_mir = 2)) Idt_TPo_MEd2,
  nvl(Idt_TPo_MEd3, (select Idt_TPo_MEd from srx_medidas_indicadores medInd
        where medind.idt_ind = ind.idt_ind and medind.ordem_mir = 3)) Idt_TPo_MEd3  
  from srx_indicadores ind
  join srx_acoes_indicadores ais on ais.idt_ind = ind.idt_ind
  join srx_programas_municipios pms on pms.idt_prog = ais.idt_prog and pms.pms_ano = ais.ano_ais  
  left join srx_programacao prog on prog.idt_AIS = ais.idt_ais and prog.idt_mun = pms.idt_mun 
where ais.Idt_Prog = :Idt_Prog and Ano_Ais = :Ano_Ais 
  and ais.idt_acao = :idt_acao and pms.idt_mun = :idt_mun
  and (idt_tec is null or (not idt_tec is null and idt_Tec = :idt_tec))";
            OracleCommand command = new OracleCommand();
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = sql;

                command.Parameters.Add(new OracleParameter("Idt_Prog", OracleType.Number));
                command.Parameters.Add(new OracleParameter("Ano_Ais", OracleType.Number));
                command.Parameters.Add(new OracleParameter("idt_Mun", OracleType.Number));
                command.Parameters.Add(new OracleParameter("idt_Acao", OracleType.Number));
                command.Parameters.Add(new OracleParameter("idt_tec", OracleType.Number));

                command.Parameters["Idt_Prog"].Value = idt_prog;
                command.Parameters["Ano_Ais"].Value = ano_Ais;
                command.Parameters["idt_Mun"].Value = idt_Mun;
                command.Parameters["idt_Acao"].Value = idt_Acao;
                command.Parameters["idt_tec"].Value = idt_tec;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }

        public static DataTable ObterMedida(string idt_tpo_Med, string login)
        {
            string sql = @"select idt_tpo_MED, dsc_tpo_Med from srx_tipos_medidas tpoMed 
where idt_tpo_MED = :idt_tpo_MED";
            OracleCommand command = new OracleCommand();
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = sql;

                command.Parameters.Add(new OracleParameter("idt_tpo_Med", OracleType.Number));

                command.Parameters["idt_tpo_Med"].Value = idt_tpo_Med;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }

        internal static void GravaProgramacao(List<StructProgramacao> programacao, string idt_Tec, string idt_Mun, string login)
        {
            string SQL_DELETE = @"DELETE FROM Serix.srx_programacao
                                   WHERE idt_AIS = :idt_AIS and idt_Tec = :idt_Tec and idt_Mun = :idt_Mun";

            string SQL_INSERT = @"insert into Serix.srx_programacao 
  (idt_prom, idt_AIS, idt_Tec, idt_Mun, idt_Tpo_Med, valor_Prog, idt_Tpo_Med2, valor_Prog2, idt_Tpo_Med3, valor_Prog3)
values
((select  nvl(max(idt_prom),1) from Serix.srx_programacao)+1
  ,:idt_AIS, :idt_Tec, :idt_Mun, :idt_Tpo_Med, :valor_Prog
    , :idt_Tpo_Med2, :valor_Prog2
    , :idt_Tpo_Med3, :valor_Prog3
)";

            string SQL_VALIDACAO = @"";

            OracleCommand command = new OracleCommand();
            OracleTransaction trans;
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL_DELETE;

                command.Parameters.Add(new OracleParameter("idt_AIS", OracleType.Number));
                command.Parameters.Add(new OracleParameter("idt_Tec", OracleType.Number));
                command.Parameters.Add(new OracleParameter("idt_Mun", OracleType.Number));
                command.Parameters["idt_Tec"].Value = idt_Tec;
                command.Parameters["idt_Mun"].Value = idt_Mun;

                foreach (StructProgramacao programa in programacao)
                {
                    command.Parameters["idt_AIS"].Value = programa.Idt_AIS;
                    command.ExecuteNonQuery();
                }

                command.Parameters.Clear();

                command.CommandText = SQL_INSERT;

                command.Parameters.Add(new OracleParameter("idt_AIS", OracleType.Number));
                command.Parameters.Add(new OracleParameter("idt_Tec", OracleType.Number));
                command.Parameters.Add(new OracleParameter("idt_Mun", OracleType.Number));

                command.Parameters["idt_Tec"].Value = idt_Tec;
                command.Parameters["idt_Mun"].Value = idt_Mun;

                command.Parameters.Add(new OracleParameter("idt_Tpo_Med", OracleType.Number));
                command.Parameters.Add(new OracleParameter("valor_Prog", OracleType.Number));
                command.Parameters.Add(new OracleParameter("idt_Tpo_Med2", OracleType.Number));
                command.Parameters.Add(new OracleParameter("valor_Prog2", OracleType.Number));
                command.Parameters.Add(new OracleParameter("idt_Tpo_Med3", OracleType.Number));
                command.Parameters.Add(new OracleParameter("valor_Prog3", OracleType.Number));

                foreach (StructProgramacao programa in programacao)
                {
                    command.Parameters["idt_AIS"].Value = programa.Idt_AIS;

                    if (programa.Idt_Tpo_Med == null || programa.valor == null)
                        continue;
                    command.Parameters["idt_Tpo_Med"].Value = programa.Idt_Tpo_Med;
                    command.Parameters["valor_Prog"].Value = programa.valor;
                    if (programa.Idt_Tpo_Med2 == null)
                    {
                        command.Parameters["idt_Tpo_Med2"].Value = DBNull.Value;// ;
                        command.Parameters["valor_Prog2"].Value = DBNull.Value;// programa.valor2;
                    }
                    else
                    {
                        command.Parameters["idt_Tpo_Med2"].Value = programa.Idt_Tpo_Med2;
                        command.Parameters["valor_Prog2"].Value = programa.valor2;
                    }
                    if (programa.Idt_Tpo_Med3 == null)
                    {
                        command.Parameters["idt_Tpo_Med3"].Value = DBNull.Value;
                        command.Parameters["valor_Prog3"].Value = DBNull.Value;
                    }
                    else
                    {
                        command.Parameters["idt_Tpo_Med3"].Value = programa.Idt_Tpo_Med3;
                        command.Parameters["valor_Prog3"].Value = programa.valor3;
                    }
                    command.ExecuteNonQuery();
                }

                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command.Connection != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }

        }

        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable ObterProgramacaoMunicipio(string ANO_PMS, string IDT_TEC, string login)
        {
            string sql = @"select mun.idt_mun, mun.nome_mun, pms.valor_PMS, pms.idt_pms, pps.idt_pps from srx_municipios mun
  left join srx_programacao_municipios pms on mun.idt_mun = pms.idt_mun
  left join SRX_PROGRAMACAO_PROGRAMAS pps on pps.Idt_Pms = pms.idt_pms
where (IDT_TEC is null or IDT_TEC = :IDT_TEC) and (pms.ANO_PMS is null or pms.ANO_PMS = :ano_PMS) and
mun.idt_mun in (
            select mun.IDT_MUN from srx_tecnicos tec
              join srx_lotacoes_tecnicos lotTec on lottec.idt_tec = tec.idt_tec
              join srx_municipios mun on mun.idt_mun = lottec.idt_mun
            where lottec.sta_lotacao = 'A' AND tec.idt_tec = :idt_tec
            
            union
            
            SELECT u.IDT_MUN  FROM SRX_MUNICIPIOS U
              join SRX_USUARIOS_MUNICIPIOS S ON (U.IDT_MUN = S.IDT_MUN) 
                AND (S.STATUS = 'A') AND (S.IDT_TEC = :IDT_TEC)
)";
            OracleCommand command = new OracleCommand();
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = sql;

                command.Parameters.Add(new OracleParameter("IDT_TEC", OracleType.Number));
                command.Parameters.Add(new OracleParameter("ANO_PMS", OracleType.Number));

                command.Parameters["IDT_TEC"].Value = IDT_TEC;
                command.Parameters["ANO_PMS"].Value = ANO_PMS;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }



        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable ObterProgramacaoPrograma(string idt_pms, string login)
        {
            string sql = @"select pms.idt_pms, prog.idt_prog, nom_prog, qtd_pps, pms.valor_pms 
from srx_programacao_municipios pms
  join srx_programas_municipios progMun on progmun.idt_mun = pms.idt_mun and progmun.pms_ano = pms.ano_pms
  join srx_programas prog on prog.idt_prog = progmun.idt_prog
  left join srx_programacao_programas pps on pms.idt_pms = pps.idt_pms and pps.idt_prog = progmun.idt_prog
where pms.idt_pms = :idt_pms";
            OracleCommand command = new OracleCommand();
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = sql;

                command.Parameters.Add(new OracleParameter("idt_pms", OracleType.Number));
                command.Parameters["idt_pms"].Value = idt_pms;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }


        public static void GravarProgramacaoPrograma(int idt_pms, Dictionary<int, int> programas, string login)
        {
            string SQL_DELETE = @"DELETE FROM Serix.srx_programacao_programas
                                   WHERE idt_pms = :idt_pms";

            string SQL_INSERT = @"insert into Serix.srx_programacao_programas
  (idt_pps, idt_pms, idt_prog, qtd_Pps)
values
((select  nvl(max(idt_pms),1) from Serix.srx_programacao_programas)+1
  ,:idt_pms, :idt_prog, :qtd_Pps)";

            OracleCommand command = new OracleCommand();
            OracleTransaction trans;
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL_DELETE;

                command.Parameters.Add(new OracleParameter("idt_pms", OracleType.Number));
                command.Parameters["idt_pms"].Value = idt_pms;

                command.ExecuteNonQuery();
                command.Parameters.Clear();


                command.CommandText = SQL_INSERT;

                command.Parameters.Add(new OracleParameter("idt_pms", OracleType.Number));
                command.Parameters["idt_pms"].Value = idt_pms;


                command.Parameters.Add(new OracleParameter("idt_prog", OracleType.Number));
                command.Parameters.Add(new OracleParameter("qtd_Pps", OracleType.Number));

                foreach (int key in programas.Keys)
                {
                    command.Parameters["idt_prog"].Value = key;
                    command.Parameters["qtd_Pps"].Value = programas[key];
                    command.ExecuteNonQuery();
                }
                command.Transaction.Commit();

            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command.Connection != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }


        public static void GravarProgramacaoMunicipio(Dictionary<int, string> programasInsert, List<int> programasRemover, string idt_Tec, string ano_PMS, string login)
        {
            string SQL_DELETE_Programas = @"
DELETE FROM Serix.srx_programacao_programas
                                   WHERE idt_pms = :idt_pms";
            string SQL_DELETE_Municipios = @"
DELETE FROM Serix.srx_programacao_municipios
                                   WHERE idt_pms = :idt_pms";

            string SQL_INSERT = @"insert into Serix.srx_programacao_municipios
  (idt_pms, idt_Tec, idt_Mun, ano_PMS, valor_PMS)
values
((select  nvl(max(idt_pms),1) from Serix.srx_programacao_municipios)+1
  ,:idt_Tec, :idt_Mun, :ano_PMS, :valor_PMS)";
            OracleCommand command = new OracleCommand();
            OracleTransaction trans;
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                

                command.Parameters.Add(new OracleParameter("idt_pms", OracleType.Number));
                
                foreach (int idt_pms in programasRemover)
                {
                    command.CommandText = SQL_DELETE_Programas;
                    command.Parameters["idt_pms"].Value = idt_pms;
                    command.ExecuteNonQuery();

                    command.CommandText = SQL_DELETE_Municipios;
                    command.ExecuteNonQuery();
                }

                
                command.Parameters.Clear();

                command.CommandText = SQL_INSERT;

                command.Parameters.Add(new OracleParameter("idt_Tec", OracleType.Number));
                command.Parameters.Add(new OracleParameter("ano_PMS", OracleType.Number));
                command.Parameters["idt_Tec"].Value = idt_Tec;
                command.Parameters["ano_PMS"].Value = ano_PMS;


                command.Parameters.Add(new OracleParameter("idt_Mun", OracleType.Number));
                command.Parameters.Add(new OracleParameter("valor_PMS", OracleType.Number));

                foreach (int key in programasInsert.Keys)
                {
                    command.Parameters["idt_Mun"].Value = key;
                    command.Parameters["valor_PMS"].Value = programasInsert[key];
                    command.ExecuteNonQuery();
                }
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            finally
            {
                if (command.Connection != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }
    }
}
